package com.enjoy.book.dao;


import com.enjoy.book.bean.Book;
import com.enjoy.book.util.DBHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author徐佳彭
 */
public class BookData implements BookDao{
    QueryRunner runner= new QueryRunner();
    private int pageIndex;

    /**
     * 添加书籍
     * @return
     */
    @Override
    public int add(String name,double price,String desc,
                   String publish,String author,long stock,String address) throws SQLException {
        Connection conn  = DBHelper.getConnection();
        String sql="insert into book(`name`,price,`desc`,publish,author,stock,address) " +
                "values(?,?,?,?,?,?,?)";
        int count = runner.update(conn,sql,name,price,desc,publish,author,stock,address);
        DBHelper.close(conn);
        return count;

    }

    /**
     * 修改书籍
     */
    @Override
    public int modify(long id, String name, double price, String desc,
                      String publish, String author, long stock, String address) throws SQLException {
        Connection conn  = DBHelper.getConnection();
        String sql="update book set `name` = ?,price =?,`desc`=?,publish = ?," +
                "author =?,stock=?,address = ? where id= ? ";
        int count = runner.update(conn,sql,name,price,desc,publish,author,stock,address,id);
        DBHelper.close(conn);
        return count;

    }

    /**
     * 删除书籍
     * @return
     */
    @Override
    public int remove(long id) throws SQLException {
        Connection conn  = DBHelper.getConnection();
        String sql="delete from book where id=? ";
        int count = runner.update(conn,sql,id);
        DBHelper.close(conn);
        return count;
    }

    /**
     * 分页查询
     * @param pageIndex
     * @param pageSize
     * pageIndex偏移量计算（pageIndex-1）*pageSize
     * 页数的计算 （id/count-1）/2+1
     * @return
     * @throws SQLException
     */
    @Override
    public List<Book> getByPage(int pageIndex, int pageSize) throws SQLException {
        this.pageIndex = pageIndex;
        Connection conn  = DBHelper.getConnection();
        String sql="select * from book limit ?,?";
        int offset = (pageIndex-1)*pageSize;
        List<Book> books=runner.query(conn,sql,new BeanListHandler<Book>(Book.class),offset,pageSize);
        return books;
    }


    @Override
    public Book getByName(String  name) throws SQLException {
        Connection conn  = DBHelper.getConnection();
        String sql="select * from book where name=?";
        Book book = runner.query(conn,sql,new BeanHandler<Book>(Book.class),name);
        DBHelper.close(conn);
        return book;
    }
    @Override
    public Book getById(long id) throws SQLException {
        Connection conn  = DBHelper.getConnection();
        String sql="select * from book where id=?";
        Book book = runner.query(conn,sql,new BeanHandler<Book>(Book.class),id);
        DBHelper.close(conn);
        return book;
    }

    /**
     * 获取书籍数量
     * @return
     */
    @Override
    public int  getCount() throws SQLException {
        Connection conn  = DBHelper.getConnection();
        String sql = "select count(id)from book";
        Number data = runner.query(conn,sql,new ScalarHandler<>());
        int count = data.intValue();
        DBHelper.close(conn);
        return count;
    }

    @Override
    public int modify(long id, int amount) throws SQLException {
        Connection conn  = DBHelper.getConnection();
        String sql="update book set stock=stock + ? where id= ? ";
        int count = runner.update(conn,sql,amount,id);
        DBHelper.close(conn);
        return count;

    }
}
